from DataPrepare.dailyFactorsWithInfluxdb.factorBase import factorBase
import cx_Oracle as oracle
import pandas as pd
from Config.myConfig import *
from Config.myConstant import *
from DataAccess.TradedayDataProcess import TradedayDataProcess

########################################################################
class AShareFinancialIndicator(factorBase):
    """记录A股收益数据等"""
    #----------------------------------------------------------------------
    def __init__(self):
        #super(buySellVolumeRatio,self).__init__()
        super().__init__()
        self.factor='dailyAShareFinancialIndicator'
        pass
    #----------------------------------------------------------------------
    def getDataFromOracleByCode(self,code,startDate,endDate):
        data=self.__getDailyDataByDateFromOracleServer(code,startDate,endDate);
        return data
        pass
    #----------------------------------------------------------------------
    #输入code=600000.SH，startdate=yyyyMMdd，endDate=yyyyMMdd
    def __getDailyDataByDateFromOracleServer(self,code,startDate=EMPTY_STRING,endDate=EMPTY_STRING):
        #获取行情数据
       
        database='wind_filesync.AShareFinancialIndicator'
        connection = oracle.connect(self.oracleConnectStr)
        cursor = connection.cursor()
        oracleStr="select S_INFO_WINDCODE as code,ANN_DT as \"date\", S_FA_EXTRAORDINARY,S_FA_DEDUCTEDPROFIT,S_FA_GROSSMARGIN,S_FA_OPERATEINCOME,S_FA_FCFF,S_FA_INTERESTDEBT,S_FA_TANGIBLEASSET,      S_FA_WORKINGCAPITAL,S_FA_RETAINEDEARNINGS,S_FA_EPS_DILUTED,S_FA_BPS,S_FA_ADMINEXPENSETOGR,S_FA_ROE_DEDUCTED,S_FA_ROA,                S_FA_DEBTTOASSETS,S_FA_CATOASSETS,S_FA_CURRENTDEBTTODEBT,S_FA_TANGIBLEASSETSTOASSETS,S_FA_TANGIBLEASSETTODEBT,S_FA_TANGASSETTOINTDEBT,  S_FA_EPS_BASIC,S_FA_EPS_DILUTED2,S_FA_OCFPS,S_FA_GRPS,S_FA_ORPS,S_FA_CFPS,S_FA_EBITPS,S_FA_FCFFPS,S_FA_FCFEPS,S_FA_NETPROFITMARGIN,     S_FA_GROSSPROFITMARGIN,S_FA_COGSTOSALES,S_FA_EXPENSETOSALES,S_FA_PROFITTOGR,S_FA_SALEEXPENSETOGR,S_FA_FINAEXPENSETOGR,S_FA_IMPAIRTOGR_TTM,S_FA_GCTOGR,S_FA_OPTOGR,S_FA_EBITTOGR,S_FA_INVTURN,S_FA_ARTURN,S_FA_CATURN,S_FA_FATURN,S_FA_ASSETSTURN,S_FA_DUPONT_ASSETSTOEQUITY,    S_FA_DUPONT_ROA,S_FA_YOYEPS_BASIC,S_FA_YOYEPS_DILUTED,S_FA_YOYOCFPS,S_FA_YOYOP,S_FA_YOYEBT,S_FA_YOYNETPROFIT,S_FA_YOYNETPROFIT_DEDUCTED,S_FA_YOYOCF,S_FA_YOYROE,S_FA_YOYBPS,S_FA_YOYASSETS,S_FA_YOYEQUITY,S_FA_YOY_TR,S_FA_YOY_OR,S_QFA_YOYGR,S_QFA_CGRGR,S_QFA_YOYSALES,       S_QFA_CGRSALES,S_QFA_YOYOP,S_QFA_CGROP,S_QFA_YOYNETPROFIT,S_QFA_CGRNETPROFIT FROM {0} ".format(database)
        if startDate==EMPTY_STRING:
            oracleStr=oracleStr+"where S_INFO_WINDCODE='{0}' order by ANN_DT".format(code)
        elif endDate==EMPTY_STRING:
            oracleStr=oracleStr+"where S_INFO_WINDCODE='{0}' and ANN_DT>={1} order by ANN_DT".format(code,startDate)
        else:
            oracleStr=oracleStr+"where S_INFO_WINDCODE='{0}' and ANN_DT>={1} and ANN_DT<={2} order by ANN_DT".format(code,startDate,endDate)
        cursor.execute(oracleStr)


        mydata = cursor.fetchall()
        mydata = pd.DataFrame(mydata,columns=['code','date','S_FA_EXTRAORDINARY','S_FA_DEDUCTEDPROFIT','S_FA_GROSSMARGIN','S_FA_OPERATEINCOME','S_FA_FCFF','S_FA_INTERESTDEBT','S_FA_TANGIBLEASSET','S_FA_WORKINGCAPITAL','S_FA_RETAINEDEARNINGS','S_FA_EPS_DILUTED','S_FA_BPS','S_FA_ADMINEXPENSETOGR','S_FA_ROE_DEDUCTED','S_FA_ROA','S_FA_DEBTTOASSETS','S_FA_CATOASSETS','S_FA_CURRENTDEBTTODEBT','S_FA_TANGIBLEASSETSTOASSETS','S_FA_TANGIBLEASSETTODEBT','S_FA_TANGASSETTOINTDEBT','S_FA_EPS_BASIC','S_FA_EPS_DILUTED2','S_FA_OCFPS','S_FA_GRPS','S_FA_ORPS','S_FA_CFPS','S_FA_EBITPS','S_FA_FCFFPS','S_FA_FCFEPS','S_FA_NETPROFITMARGIN','S_FA_GROSSPROFITMARGIN','S_FA_COGSTOSALES','S_FA_EXPENSETOSALES','S_FA_PROFITTOGR','S_FA_SALEEXPENSETOGR','S_FA_FINAEXPENSETOGR','S_FA_IMPAIRTOGR_TTM','S_FA_GCTOGR','S_FA_OPTOGR','S_FA_EBITTOGR','S_FA_INVTURN','S_FA_ARTURN','S_FA_CATURN','S_FA_FATURN','S_FA_ASSETSTURN','S_FA_DUPONT_ASSETSTOEQUITY','S_FA_DUPONT_ROA','S_FA_YOYEPS_BASIC','S_FA_YOYEPS_DILUTED','S_FA_YOYOCFPS','S_FA_YOYOP','S_FA_YOYEBT','S_FA_YOYNETPROFIT','S_FA_YOYNETPROFIT_DEDUCTED','S_FA_YOYOCF','S_FA_YOYROE','S_FA_YOYBPS','S_FA_YOYASSETS','S_FA_YOYEQUITY','S_FA_YOY_TR','S_FA_YOY_OR','S_QFA_YOYGR','S_QFA_CGRGR','S_QFA_YOYSALES','S_QFA_CGRSALES','S_QFA_YOYOP','S_QFA_CGROP','S_QFA_YOYNETPROFIT','S_QFA_CGRNETPROFIT'])
        mydata[['S_FA_EXTRAORDINARY','S_FA_DEDUCTEDPROFIT','S_FA_GROSSMARGIN','S_FA_OPERATEINCOME','S_FA_FCFF','S_FA_INTERESTDEBT','S_FA_TANGIBLEASSET','S_FA_WORKINGCAPITAL','S_FA_RETAINEDEARNINGS','S_FA_EPS_DILUTED','S_FA_BPS','S_FA_ADMINEXPENSETOGR','S_FA_ROE_DEDUCTED','S_FA_ROA','S_FA_DEBTTOASSETS','S_FA_CATOASSETS','S_FA_CURRENTDEBTTODEBT','S_FA_TANGIBLEASSETSTOASSETS','S_FA_TANGIBLEASSETTODEBT','S_FA_TANGASSETTOINTDEBT','S_FA_EPS_BASIC','S_FA_EPS_DILUTED2','S_FA_OCFPS','S_FA_GRPS','S_FA_ORPS','S_FA_CFPS','S_FA_EBITPS','S_FA_FCFFPS','S_FA_FCFEPS','S_FA_NETPROFITMARGIN','S_FA_GROSSPROFITMARGIN','S_FA_COGSTOSALES','S_FA_EXPENSETOSALES','S_FA_PROFITTOGR','S_FA_SALEEXPENSETOGR','S_FA_FINAEXPENSETOGR','S_FA_IMPAIRTOGR_TTM','S_FA_GCTOGR','S_FA_OPTOGR','S_FA_EBITTOGR','S_FA_INVTURN','S_FA_ARTURN','S_FA_CATURN','S_FA_FATURN','S_FA_ASSETSTURN','S_FA_DUPONT_ASSETSTOEQUITY','S_FA_DUPONT_ROA','S_FA_YOYEPS_BASIC','S_FA_YOYEPS_DILUTED','S_FA_YOYOCFPS','S_FA_YOYOP','S_FA_YOYEBT','S_FA_YOYNETPROFIT','S_FA_YOYNETPROFIT_DEDUCTED','S_FA_YOYOCF','S_FA_YOYROE','S_FA_YOYBPS','S_FA_YOYASSETS','S_FA_YOYEQUITY','S_FA_YOY_TR','S_FA_YOY_OR','S_QFA_YOYGR','S_QFA_CGRGR','S_QFA_YOYSALES','S_QFA_CGRSALES','S_QFA_YOYOP','S_QFA_CGROP','S_QFA_YOYNETPROFIT','S_QFA_CGRNETPROFIT']] = mydata[['S_FA_EXTRAORDINARY','S_FA_DEDUCTEDPROFIT','S_FA_GROSSMARGIN','S_FA_OPERATEINCOME','S_FA_FCFF','S_FA_INTERESTDEBT','S_FA_TANGIBLEASSET','S_FA_WORKINGCAPITAL','S_FA_RETAINEDEARNINGS','S_FA_EPS_DILUTED','S_FA_BPS','S_FA_ADMINEXPENSETOGR','S_FA_ROE_DEDUCTED','S_FA_ROA','S_FA_DEBTTOASSETS','S_FA_CATOASSETS','S_FA_CURRENTDEBTTODEBT','S_FA_TANGIBLEASSETSTOASSETS','S_FA_TANGIBLEASSETTODEBT','S_FA_TANGASSETTOINTDEBT','S_FA_EPS_BASIC','S_FA_EPS_DILUTED2','S_FA_OCFPS','S_FA_GRPS','S_FA_ORPS','S_FA_CFPS','S_FA_EBITPS','S_FA_FCFFPS','S_FA_FCFEPS','S_FA_NETPROFITMARGIN','S_FA_GROSSPROFITMARGIN','S_FA_COGSTOSALES','S_FA_EXPENSETOSALES','S_FA_PROFITTOGR','S_FA_SALEEXPENSETOGR','S_FA_FINAEXPENSETOGR','S_FA_IMPAIRTOGR_TTM','S_FA_GCTOGR','S_FA_OPTOGR','S_FA_EBITTOGR','S_FA_INVTURN','S_FA_ARTURN','S_FA_CATURN','S_FA_FATURN','S_FA_ASSETSTURN','S_FA_DUPONT_ASSETSTOEQUITY','S_FA_DUPONT_ROA','S_FA_YOYEPS_BASIC','S_FA_YOYEPS_DILUTED','S_FA_YOYOCFPS','S_FA_YOYOP','S_FA_YOYEBT','S_FA_YOYNETPROFIT','S_FA_YOYNETPROFIT_DEDUCTED','S_FA_YOYOCF','S_FA_YOYROE','S_FA_YOYBPS','S_FA_YOYASSETS','S_FA_YOYEQUITY','S_FA_YOY_TR','S_FA_YOY_OR','S_QFA_YOYGR','S_QFA_CGRGR','S_QFA_YOYSALES','S_QFA_CGRSALES','S_QFA_YOYOP','S_QFA_CGROP','S_QFA_YOYNETPROFIT','S_QFA_CGRNETPROFIT']].astype('float')
        mydata['mytime']=pd.to_datetime(mydata['date'],format='%Y%m%d')
        mydata.set_index('mytime',inplace=True,drop=True)
        mydata = mydata.drop_duplicates(subset=['code', 'date'], keep='last')
        return mydata  
########################################################################
